﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using Medianamik.Core.Configuration;
using Medianamik.Core.Sugar;

namespace Medianamik.Core.CodeGeneration
{
    public class SqlGenerator
    {
        private static readonly MedianamikConfigSection Config =
            (MedianamikConfigSection)ConfigurationManager.GetSection(ConfigurationSectionName.MEDIANAMIK);

        #region Constants

        //On supprime toutes les propriétés publiées qui restent
        private const string DeleteNPV =
            @"DELETE medianamik_NodePropertyValue
                FROM medianamik_NodePropertyValue v
                INNER JOIN medianamik_Node n on n.NodeId = v.NodeId
                WHERE v.Revision IS NOT NULL
                AND n.TypeId = @TypeId
                ";

        //Si des propriétés sont publiées, on les remet en Draft
        private const string UpdateNPV =
            @"UPDATE medianamik_NodePropertyValue 
                SET Revision = NULL
                FROM medianamik_NodePropertyValue v
                INNER JOIN medianamik_Node n ON v.NodeId = n.NodeId
                WHERE v.EndRevision IS NULL
                AND n.TypeId = @TypeId
                ";

        private const string DestroyDeletedNodesStatement =
            @"
                DECLARE @t as Table( NodeId UNIQUEIDENTIFIER)
                ;WITH Nodes as (
                select * from medianamik_Node
                WHERE IsDeleted = 1
                UNION ALL
                SELECT medianamik_Node.* from  medianamik_Node
                INNER JOIN Nodes on Nodes.NodeId = medianamik_Node.ParentNodeId
                )
                Insert into @t
                select NodeId from Nodes 
                GROUP BY NodeId, Depth
                ORDER BY Depth DESC

                DELETE Medianamik_Node
                FROM @t t INNER JOIN Medianamik_Node n ON t.NodeId = n.NodeId";

        private const string CleanUpGenTablesStatement =
            @"
                DECLARE @q nvarchar(max) 
                SET @q = ''
                select @q = @q + 'DELETE FROM [' + t.TABLE_SCHEMA + '].[' + t.table_name + '] 
                WHERE Generation > 0
                ' from information_schema.Tables t
                INNER JOIN Information_SCHEMA.Columns c on t.Table_Name = c.Table_Name
                where t.table_name like 'gen_%'
                AND c.column_Name = 'Generation'

                EXEC sp_executesql @q";

        private const string CleanUpRevisionsStatement =
            @"delete{1} medianamik_NodeRevision from medianamik_NodeRevision fred inner join
                (select * from
                (SELECT nodeid, publishedon, rank() over(partition by nodeid,Ranking  order by publishedon asc) as 'PublishRank' FROM 
                (select *, rank() over(partition by nodeid, culturename order by publishedon desc) as 'Ranking'
                from medianamik_NodeRevision where publishedon is not null) testing
                where Ranking = {0}) testing2
                where PublishRank = 1) rambo on rambo.nodeid = fred.nodeid and rambo.publishedon >= fred.publishedon";

        #endregion

        private static readonly DefaultColumns DefaultColumns = new DefaultColumns();

        public string BuildTableExistsStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);

            return "SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('" +
                   names.TableName + "') AND type in (N'U')";
        }

        public string BuildInsertStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);

            var nodeTypeColumns = nodeType.GetAllRefreshedProperties()
                .Where(p => p.DataType != null)
                .Where(p => p.IsGenerated)
                .Select(p => new TableColumn(p));

            var allColumns = nodeTypeColumns.Concat(DefaultColumns.GenTableColumns);

            var allColumnsStr = allColumns.Aggregate(new StringBuilder()
                 , (sb, c) => sb.Append(c.Name + ",")
                 , sb => sb.ToString().TrimEnd(',', ' '));

            var paramNamesStr = allColumns.Aggregate(new StringBuilder()
                 , (sb, c) => sb.Append(c.ParameterName + ",")
                 , sb => sb.ToString().TrimEnd(',', ' '));

            var query = "INSERT INTO " + names.TableName + " ( " + allColumnsStr + " ) ";
            query += " VALUES (" + paramNamesStr + " ) ";

            return query;
        }

        public string BuildUpdateStatement(NodeInstance nodeInstance)
        {
            var nodeType = nodeInstance.Node.NodeType;

            var names = new SqlNameGenerator(nodeType);

            var allColumns = nodeType.GetAllRefreshedProperties()
                .Where(p => p.DataType != null)
                .Where(p => p.IsGenerated)
                .Select(p => new TableColumn(p))
                .Concat(DefaultColumns.GenTableColumns).ToList();

            var queryBuilder = new StringBuilder("UPDATE ");
            queryBuilder.Append(names.TableName);
            queryBuilder.Append("\nSET\n");

            allColumns.Each((c, i) =>
               {
                   queryBuilder.Append(c.Name);
                   queryBuilder.Append(" = ");
                   queryBuilder.Append(c.ParameterName);

                   if (i < allColumns.Count - 1)
                   {
                       queryBuilder.Append(",");
                   }

                   queryBuilder.Append("\n");
               });

            queryBuilder.Append("WHERE NodeId = '");
            queryBuilder.Append(nodeInstance.Node.NodeId);
            queryBuilder.Append("'\n AND CultureName = '");
            queryBuilder.Append(nodeInstance.Culture.Name);
            queryBuilder.Append("'\n AND Generation = -1");

            return queryBuilder.ToString();
        }

        public string BuildDeleteDraftAndPublishedStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);

            //On construit une requete pour supprimer toutes les revisions
            var query = string.Concat("DELETE FROM " + names.TableName,
                                      " WHERE " + DefaultColumns.NodeId.Name + " = " +
                                      DefaultColumns.NodeId.ParameterName,
                                      " AND " + DefaultColumns.CultureName.Name + " = " +
                                      DefaultColumns.CultureName.ParameterName);

            return query;
        }

        public string BuildDeleteDraftOrPublishedStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);

            //On construit une requete pour supprimer toutes les revisions
            var query = string.Concat("DELETE FROM " + names.TableName,
                                      " WHERE " + DefaultColumns.NodeId.Name + " = " +
                                      DefaultColumns.NodeId.ParameterName,
                                      " AND " + DefaultColumns.CultureName.Name + " = " +
                                      DefaultColumns.CultureName.ParameterName,
                                      " AND " + DefaultColumns.Generation.Name + " = " +
                                      DefaultColumns.Generation.ParameterName);

            return query;
        }

        public string BuildDropTableStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);

            var query = "IF EXISTS (Select * from sys.objects WHERE object_id = OBJECT_ID('" + names.TableName + "'))";
            query += " BEGIN EXEC ('DROP TABLE " + names.TableName + "') END";

            return query;
        }

        public string BuildCreateTableStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);

            var query = string.Concat("CREATE TABLE ", names.TableName,
                " ([Id] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),", GetTypeColumns(nodeType, true),
                " CONSTRAINT ", names.PrimaryKeyName,
                " PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]", ") ON [PRIMARY] ; ", string.Format(
              @" CREATE NONCLUSTERED INDEX [IX_{1}_PATH] ON {0}
                (
	                [NodeId] ASC
                )
                INCLUDE ( [ParentNodeId],
                [TypeId],
                [CreatedOn],
                [ModifiedOn],
                [Path],
                [Depth],
                [Position]) ; ",
                names.TableName, Guid.NewGuid().ToString("N")));

            return query;
        }

        private string GetTypeColumns(NodeType nodeType, bool createTable)
        {
            var baseColumns = DefaultColumns.GenTableColumns
                .Aggregate(new StringBuilder(),
                   (sb, c) => sb.Append(c.ToCreateTableString(createTable) + ","),
                   sb => sb.ToString().TrimEnd(new[] { ',', ' ' }));

            var typeColumns = nodeType.GetAllRefreshedProperties()
                .Where(p => p.DataType != null)
                .Where(p => p.IsGenerated)
                .Select(p => new TableColumn(p));

            var typeColumnsStr = typeColumns.Aggregate(new StringBuilder(),
               (sb, c) => sb.Append(c.ToCreateTableString(createTable) + ","),
               sb => sb.ToString().TrimEnd(new[] { ',', ' ' }));

            return string.Concat(baseColumns, typeColumns.Any() ? "," + typeColumnsStr : string.Empty);
        }

        private string GetInterfaceColumns(NodeTypeInterface nodeTypeInterface)
        {
            var baseColumns = DefaultColumns.GenTableColumns
                .Aggregate(new StringBuilder(),
                   (sb, c) => sb.Append(c.Name + ","),
                   sb => sb.ToString().TrimEnd(new[] { ',', ' ' }));

            var interfaceColumns = nodeTypeInterface.Properties
                .Where(p => p.DataType != null)
                .Select(p => new TableColumn(p));

            var typeColumnsStr = interfaceColumns.Aggregate(new StringBuilder(),
               (sb, c) => sb.Append(c.Name + ","),
               sb => sb.ToString().TrimEnd(new[] { ',', ' ' }));

            return string.Concat(baseColumns, interfaceColumns.Any() ?
                "," + typeColumnsStr : string.Empty);
        }

        public string BuildAddColumnStatement(NodeTypeProperty pNodeTypeProperty, NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);
            var column = new TableColumn(pNodeTypeProperty);
            var query = " ALTER TABLE " + names.TableName
                        + " ADD  " + column.ToCreateTableString();

            return query;
        }

        public string BuildRemoveColumnStatement(NodeTypeProperty pNodeTypeProperty, NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);
            var column = new TableColumn(pNodeTypeProperty);
            var query = " ALTER TABLE " + names.TableName
                        + " DROP  COLUMN " + column.Name;

            return query;
        }

        public string BuildDisableTableTriggersStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);
            var builder = new StringBuilder();
            builder.Append("ALTER TABLE ")
                .Append(names.TableName)
                .Append(" DISABLE TRIGGER ALL");

            return builder.ToString();
        }

        public string BuildEnableTableTriggersStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);
            var builder = new StringBuilder();
            builder.Append("ALTER TABLE ")
                .Append(names.TableName)
                .Append(" ENABLE TRIGGER ALL");

            return builder.ToString();
        }

        public string BuildSwitchToNonVersionableTypeMaintenanceStatement(NodeType nodeType)
        {
            //On supprime aussi le data de la table générée
            var names = new SqlNameGenerator(nodeType);

            var deleteGen = new StringBuilder(@" DELETE FROM ")
                .Append(names.TableName)
                .AppendLine(" WHERE ")
                .Append(DefaultColumns.Generation.Name).Append(" <> -1 ");

            return new StringBuilder()
                .AppendLine(UpdateNPV)
                .AppendLine(DeleteNPV)
                .Append(deleteGen)
                .ToString();
        }

        public string BuildRemoveNeutralLangInstancesForNonNeutralLangNodeStatement(IEnumerable<NodeType> nodeTypes)
        {
            var cleanUpScript =
                @"delete from medianamik_nodeinstance
                where nodeid in(select nodeid from medianamik_node
                where typeid in ('" +
                nodeTypes.Select(t => t.ID).ToString("','") + @"'))
                and culturename = ''";

            return cleanUpScript;
        }

        public string BuildRemoveNonNeutralLangInstancesForNeutralLangNodeStatement(IEnumerable<NodeType> nodeTypes)
        {
            var cleanUpScript =
                @"delete from medianamik_nodeinstance
                where nodeid in(select nodeid from medianamik_node
                where typeid in ('" +
                nodeTypes.Select(t => t.ID).ToString("','") + @"'))
                and culturename <> ''";

            return cleanUpScript;
        }

        public string BuildDestroyDeletedNodesStatement()
        {
            return DestroyDeletedNodesStatement;
        }

        public string BuildCleanUpGenTablesStatement()
        {
            return CleanUpGenTablesStatement;
        }

        public string BuildCleanUpRevisionsStatement(bool top)
        {
            return String.Format(CleanUpRevisionsStatement,  (Config.MaximumRevisions +1), top ? " top(100)" : "");
        }

        public string BuildDisableTableConstraintsStatement(string tableName)
        {
            var builder = new StringBuilder();
            builder.Append("ALTER TABLE ").Append(tableName)
                .Append(" NOCHECK CONSTRAINT ALL");

            return builder.ToString();
        }

        public string BuildEnableTableConstraintsStatement(string tableName)
        {
            var builder = new StringBuilder();
            builder.Append("ALTER TABLE ").Append(tableName)
                .Append(" CHECK CONSTRAINT ALL");

            return builder.ToString();
        }

        public string BuildEnableTableConstraintStatement(string tableName, string constraintName)
        {
            var builder = new StringBuilder();
            builder.Append("ALTER TABLE ").Append(tableName)
                .Append(" CHECK CONSTRAINT ").Append(constraintName);

            return builder.ToString();
        }

        public string BuildDisableTableConstraintStatement(string tableName, string constraintName)
        {
            var builder = new StringBuilder();
            builder.Append("ALTER TABLE ").Append(tableName)
                .Append(" NOCHECK CONSTRAINT ").Append(constraintName);

            return builder.ToString();
        }


        #region Views

        public string BuildCreateViewStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);

            var childTypes = NodeTypeManager.GetAllChildTypes(nodeType.ID)
                .Where(t => t.IsGenerated)
                .OrderBy(t => t.Depth);

            var fields = GetTypeColumns(nodeType, false);
            //var medianamikNodeColumns = new[]
            //        {
            //            "[CreatedOn]", "[CreatedBy]", "[Depth]", "[ParentNodeId]", "[Path]", "[Position]", "[TypeId]",
            //            "[ModifiedOn]"
            //        };

            //var fieldsList = fields.Split(',').ToList();
            //fieldsList.RemoveAll(s => medianamikNodeColumns.Any(s2 => s2.Equals(s, StringComparison.OrdinalIgnoreCase)));
            //fields = fieldsList.ToString(",");


            var relations = nodeType.GetOneToManyRelations().ToList();

            var sqlBuilder = new StringBuilder("CREATE VIEW ");
            sqlBuilder.Append(names.ViewName);
            sqlBuilder.Append(" AS ");
            //sqlBuilder.Append(" AS Select result.*, " + medianamikNodeColumns.ToString("n.", ", ", null, null, true, true, null, null, null, " ") + " from ( ");

            sqlBuilder.Append(BuildPartialSelectView(nodeType, fields, names.TableName, relations));

            childTypes.ForEach(t => sqlBuilder.Append("\n UNION ALL " +
                BuildPartialSelectView(t, fields, new SqlNameGenerator(t).TableName, relations)));

            //sqlBuilder.Append(" ) as result  inner join Medianamik_Node n on result.NodeId = n.NodeId ");

            return sqlBuilder.ToString();
        }

        private string BuildPartialSelectView(NodeType nodeType, string fields, string tableName, List<OneToManyRelation> relations)
        {
            var sqlSelect = new StringBuilder();

            fields = fields.Replace("[", "src.[");
            var suffixTable = new StringBuilder();

            // NON Versioned
            if (!nodeType.IsVersioned || nodeType.IsManyToMany)
            {
                fields = fields.Replace("src.[Generation]", "gn.[Generation]");
                suffixTable.Append("cross join dbo.medianamik_Generation as gn ");
            }

            // Neutral language
            if ((nodeType.IsLanguageNeutral || nodeType.IsManyToMany) && Config.InvariantCultureViews)
            {
                fields = fields.Replace("src.[CultureName]", "ct.[CultureName]");
                suffixTable.Append("cross join dbo.medianamik_Culture as ct ");
            }

            // FIX EF4.1 Many-To-Many with composite key
            if (nodeType.IsManyToMany && Config.InvariantCultureViews)
            {
                fields += ", gn.[Generation] as GenerationLeft,gn.[Generation] as GenerationRight";
                fields += ", ct.[CultureName] as CultureNameLeft,ct.[CultureName] as CultureNameRight";
            }

            sqlSelect.AppendFormat("SELECT [{0}].* FROM (", nodeType.Name);
            sqlSelect.Append(" SELECT src.[Id],");
            sqlSelect.Append(fields);
            sqlSelect.AppendFormat(" FROM {0} as src ", tableName);
            sqlSelect.Append(suffixTable);
            sqlSelect.AppendFormat(") as [{0}] ", nodeType.Name);

            return sqlSelect.ToString();
        }

        public string BuildCreateViewStatement(NodeTypeInterface pInterface, IEnumerable<NodeType> implementers)
        {
            if (implementers.Count() < 1)
                throw new ApplicationException("Cannot create view for interface " + pInterface.Name +
                                               " because no types implement it.");

            var implementingTypes = new List<NodeType>(implementers);
            var names = new SqlInterfaceNameGenerator(pInterface);
            var select = " SELECT [Id]," + GetInterfaceColumns(pInterface) + " FROM ";

            var sqlBuilder = new StringBuilder("CREATE VIEW ");
            sqlBuilder.Append(names.ViewName);
            sqlBuilder.Append(" AS ");

            for (int i = 0; i < implementingTypes.Count; i++)
            {
                if (i > 0)
                {
                    sqlBuilder.Append("\n UNION ALL ");
                }
                sqlBuilder.Append(select + new SqlNameGenerator(implementingTypes[i]).TableName);
            }

            return sqlBuilder.ToString();
        }

        public string BuildDropViewStatement(NodeType nodeType)
        {
            var names = new SqlNameGenerator(nodeType);

            var query = "IF EXISTS (Select * from sys.objects WHERE object_id = OBJECT_ID('" + names.ViewName + "'))";
            query += " BEGIN EXEC ('DROP VIEW " + names.ViewName + "') END";

            return query;
        }

        public string BuildDropViewStatement(NodeTypeInterface pInterface)
        {
            var names = new SqlInterfaceNameGenerator(pInterface);

            var query = "IF EXISTS (Select * from sys.objects WHERE object_id = OBJECT_ID('" + names.ViewName + "'))";
            query += " BEGIN EXEC ('DROP VIEW " + names.ViewName + "') END";

            return query;
        }

        #endregion
    }
}